#该程序用于手动初始化pop_record表的热度值与热度记录
#数据量较大的情况下容易出现漏执行，不建议使用该方式
#若需要重算部分错误记录，请加入判断语句


import mysql.connector
import math
from time import sleep

from datetime import datetime, timedelta
import pop_value_cal

# 连接MySQL数据库
cnx = mysql.connector.connect(
    host="sse-21311572-train.mysql.rds.aliyuncs.com",
    port="3307",
    user="SSE_user1",
    password="SSE_user1test",
    database="sse_training",
    charset='utf8'
)
cursor = cnx.cursor()


# 设置重力因子
gravity_factor = 0.1

# 获取现在的时间
now = datetime.now()

# 查询pop_record表和post表的数据，并进行联合查询
query = """
    SELECT pr.pid, pr.viewNum, pr.likeNum, pr.comNum, pr.c_likeNum, p.time, pr.id
    FROM pop_record pr
    JOIN post p ON pr.pid = p.id
"""
cursor.execute(query)
result = cursor.fetchall()

for row in result:
    pid = row[0]
    views = row[1]
    likes = row[2]
    total_comments = row[3]
    total_comment_likes = row[4]
    post_time = row[5]
    id = row[6]

    # 计算days_since_posted
    days_since_posted = (now - post_time) / timedelta(days=0.5)

    # 调用计算热度值函数
    hotness = pop_value_cal.calculate_post_hotness(views, likes, total_comments, total_comment_likes, days_since_posted, gravity_factor)

    # 更新pop_record表中的hotVal字段
    update_query = "UPDATE pop_record SET hotVal = %s WHERE id = %s"
    cursor.execute(update_query, (hotness, id))



#以下为hotVal_rate的重计算



# 查询所有记录行，按照帖子id和时间排序
query = "SELECT pid, hotVal_rate, viewNum, likeNum, comNum, c_likeNum, recordTime FROM pop_record ORDER BY pid, recordTime"
cursor.execute(query)
rows = cursor.fetchall()


# 遍历记录行
for i in range(len(rows)):
    pid, hotVal_rate, viewNum, likeNum, comNum, c_likeNum, time = rows[i]
    #通过是否有浏览量判断帖子记录是否有效（一般而言不会有0浏览量的帖子存在）
    if viewNum:
        # 检查是否存在上一次记录
        if i > 0 and rows[i - 1][0] == pid:
            last_hotVal_rate, last_viewNum, last_likeNum, last_comNum, last_c_likeNum, last_time = rows[i - 1][-6:]

            # 计算时间差
            time_diff = time - last_time
            hours_diff = time_diff.total_seconds() / 3600

            # 检查时间差是否大于等于1小时
            if hours_diff >= 0.9:
                # 计算hotVal_rate的差值
                hotVal_rate_diff = 0.02 * viewNum + likeNum + comNum + c_likeNum - (0.02 * last_viewNum + last_likeNum + last_comNum + last_c_likeNum)

                # 计算插值
                hotVal_rate = hotVal_rate_diff / hours_diff

                # 更新记录的hotVal_rate
                update_query = "UPDATE pop_record SET hotVal_rate = %s WHERE pid = %s AND recordTime = %s"
                update_values = (hotVal_rate, pid, time)
                cursor.execute(update_query, update_values)
            else:
                # 寻找满足条件的记录
                found = False
                for j in range(i - 2, -1, -1):
                    prev_pid, prev_hotVal_rate, prev_viewNum, prev_likeNum, prev_comNum, prev_c_likeNum, prev_time = rows[j]
                    if prev_pid != pid:
                        #到达该帖子最早记录边界
                        break
                    prev_time_diff = last_time - prev_time
                    prev_hours_diff = prev_time_diff.total_seconds() / 3600

                    if prev_hours_diff >= 1:
                        # 计算hotVal_rate的差值
                        hotVal_rate_diff = 0.02 * last_viewNum + last_likeNum + last_comNum + last_c_likeNum - (0.02 * prev_viewNum + prev_likeNum + prev_comNum + prev_c_likeNum)

                        # 计算插值
                        hotVal_rate = hotVal_rate_diff / prev_hours_diff

                        # 更新记录的hotVal_rate
                        update_query = "UPDATE pop_record SET hotVal_rate = %s WHERE pid = %s AND recordTime = %s"
                        update_values = (hotVal_rate, pid, time)
                        cursor.execute(update_query, update_values)
                        found = True
                        break
                    

                if not found:
                    update_query = "UPDATE pop_record SET hotVal_rate = %s WHERE pid = %s AND recordTime = %s"
                    update_values = (last_hotVal_rate, pid, time)
                    cursor.execute(update_query, update_values)
        else:
            update_query = "UPDATE pop_record SET hotVal_rate = %s WHERE pid = %s AND recordTime = %s"
            update_values = (0, pid, time)
            cursor.execute(update_query, update_values)
                
# 提交更改并关闭数据库连接
cnx.commit()
cursor.close()
cnx.close()